package com.campus.dao.impl;

import com.campus.dao.ClassDao;
import com.campus.dto.ClassConditionDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.campus.model.Class;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
@Slf4j
public class ClassDaoImpl implements ClassDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public boolean isActivityPublished(int activityId) {
        String sql = "SELECT status FROM t_manager_activity WHERE id = ?";
        try {
            Integer status = jdbcTemplate.queryForObject(sql, Integer.class, activityId);
            return status != null && status == 1; // 检查活动是否已发布
        } catch (Exception e) {
            log.error("检查活动发布状态时出错: {}", e.getMessage(), e);
        }
        return false; // 查询失败时返回 false
    }

    @Override
    public int createClass(Class clazz) {
        // 检查课程名称是否重复
        if (isClassNameDuplicate(clazz.getName(), clazz.getActivityId())) {
            log.warn("班级名称 {} 在活动 {} 中重复", clazz.getName(), clazz.getActivityId());
            return -1;  // 返回 -1 表示课程名称重复
        }

        // 检查 activityId 是否为 null，若为 null 则返回 -1，表示创建失败
        if (clazz.getActivityId() == null) {
            log.warn("活动 ID 为空: {}", clazz);
            return -1;
        }

        // 插入教学班的 SQL
        String sql = "INSERT INTO t_manager_class (name, class_desc, activity_id, teacher1_id, teacher2_id, created_time) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        try {
            int rowsAffected = jdbcTemplate.update(sql,
                    clazz.getName(),
                    clazz.getClassDesc(),
                    clazz.getActivityId(),
                    clazz.getTeacher1Id(),
                    clazz.getTeacher2Id(),
                    new java.sql.Timestamp(System.currentTimeMillis()));
            if (rowsAffected > 0) {
                // 获取生成的主键
                String query = "SELECT LAST_INSERT_ID()";
                return jdbcTemplate.queryForObject(query, Integer.class);
            }
        } catch (Exception e) {
            log.error("创建班级时出错: {}", e.getMessage(), e);
        }
        return -1;  // 如果发生异常，返回 -1
    }

    @Override
    public int updateClass(Class clazz) {
        String sql = "UPDATE t_manager_class " +
                "SET name = ?, class_desc = ?, teacher1_id = ?, teacher2_id = ?, activity_id = ? " +
                "WHERE id = ?";
        try {
            return jdbcTemplate.update(sql,
                    clazz.getName(),
                    clazz.getClassDesc(),
                    clazz.getTeacher1Id(),
                    clazz.getTeacher2Id(),
                    clazz.getActivityId(),
                    clazz.getId());
        } catch (Exception e) {
            log.error("更新班级时出错: {}", e.getMessage(), e);
        }
        return 0; // 如果更新失败，返回 0
    }

    @Override
    public int deleteClass(int classId) {

        // 1. 查找与 classId 关联的 student_id
        String getStuInClassIdsSql = "SELECT id FROM t_teaching_student_in_class WHERE class_id = ?";
        List<Integer> stuInClassIds = jdbcTemplate.queryForList(getStuInClassIdsSql, Integer.class, classId);

        // 2. 如果有 stu_in_class_id，删除相关的评分、学习记录和教学评价
        if (!stuInClassIds.isEmpty()) {
            // 删除学生评分记录
            String deleteStudentScoringSql = "DELETE FROM t_teaching_student_scoring WHERE stu_in_class_id = ?";
            for (Integer stuInClassId : stuInClassIds) {
                jdbcTemplate.update(deleteStudentScoringSql, stuInClassId);
            }

            // 删除学生学习记录
            String deleteStudentLearningSql = "DELETE FROM t_teaching_student_learning WHERE stu_in_class_id = ?";
            for (Integer stuInClassId : stuInClassIds) {
                jdbcTemplate.update(deleteStudentLearningSql, stuInClassId);
            }

            // 删除教学评价记录
            String deleteTeachingEvaluationSql = "DELETE FROM t_teaching_evaluation WHERE stu_in_class_id = ?";
            for (Integer stuInClassId : stuInClassIds) {
                jdbcTemplate.update(deleteTeachingEvaluationSql, stuInClassId);
            }
            // 8. 删除点名详细记录
            String deleteCallingDetailSql = "DELETE FROM t_teaching_calling_detail WHERE stu_in_class_id = ?";
            for (Integer stuInClassId : stuInClassIds) {
                jdbcTemplate.update(deleteCallingDetailSql, stuInClassId);
            }

        }

        // 3. 删除 `t_teaching_student_in_class` 中与 classId 关联的记录
        String deleteStudentInClassSql = "DELETE FROM t_teaching_student_in_class WHERE class_id = ?";
        jdbcTemplate.update(deleteStudentInClassSql, classId);

        // 4. 查找与 classId 关联的课程 ID
        String getCourseIdsSql = "SELECT id FROM t_course WHERE class_id = ?";
        List<Integer> courseIds = jdbcTemplate.queryForList(getCourseIdsSql, Integer.class, classId);

        // 5. 删除与课程关联的学习单元、教学评价和课程信息
        if (!courseIds.isEmpty()) {
            // 删除与课程关联的学习单元
            String deleteUnitsSql = "DELETE FROM t_course_learn_unit WHERE course_id = ?";
            for (Integer courseId : courseIds) {
                jdbcTemplate.update(deleteUnitsSql, courseId);
            }

            // 删除课程信息
            String deleteCoursesSql = "DELETE FROM t_course WHERE id = ?";
            for (Integer courseId : courseIds) {
                jdbcTemplate.update(deleteCoursesSql, courseId);
            }
        }

        // 7. 删除与班级关联的点名记录
        String deleteCallingSql = "DELETE FROM t_teaching_calling WHERE class_id = ?";
        jdbcTemplate.update(deleteCallingSql, classId);


        // 9. 删除教学班信息
        String deleteClassSql = "DELETE FROM t_manager_class WHERE id = ?";
        return jdbcTemplate.update(deleteClassSql, classId);
    }

    @Override
    public List<Class> getClassesByActivityId(int activityId) {
        String sql = "SELECT * FROM t_manager_class WHERE activity_id = ?";
        try {
            return jdbcTemplate.query(sql, new Object[]{activityId}, new RowMapper<Class>() {
                @Override
                public Class mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return new Class()
                            .setId(rs.getInt("id"))
                            .setName(rs.getString("name"))
                            .setClassDesc(rs.getString("class_desc"))
                            .setTeacher1Id(rs.getInt("teacher1_id"))
                            .setTeacher2Id(rs.getInt("teacher2_id"))
                            .setActivityId(rs.getInt("activity_id"))
                            .setCreatedTime(rs.getTimestamp("created_time"));
                }
            });
        } catch (Exception e) {
            log.error("查询班级时出错: {}", e.getMessage(), e);
        }
        return null; // 如果查询失败，返回 null
    }

    // 新增方法：检查课程名称是否在同一活动下重复
    @Override
    public boolean isClassNameDuplicate(String className, int activityId) {
        String sql = "SELECT COUNT(*) FROM t_manager_class WHERE name = ? AND activity_id = ?";
        try {
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, className, activityId);
            return count != null && count > 0; // 如果数量大于 0，则课程名称在同一活动下重复
        } catch (Exception e) {
            log.error("检查课程名称是否重复时出错: {}", e.getMessage(), e);
        }
        return false; // 查询失败时返回不重复
    }

    @Override
    public List<Class> getClassesByCondition(ClassConditionDto condition) {
        StringBuilder sql = new StringBuilder("SELECT * FROM t_manager_class WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 动态拼接查询条件
        if (condition.getName() != null && !condition.getName().isEmpty()) {
            sql.append(" AND name LIKE ?");
            params.add("%" + condition.getName() + "%");
        }
        if (condition.getActivityId() != null) {
            sql.append(" AND activity_id = ?");
            params.add(condition.getActivityId());
        }
        if (condition.getClassDesc() != null && !condition.getClassDesc().isEmpty()) {
            sql.append(" AND class_desc LIKE ?");
            params.add("%" + condition.getClassDesc() + "%");
        }

        return jdbcTemplate.query(sql.toString(), params.toArray(), new RowMapper<Class>() {
            @Override
            public Class mapRow(ResultSet rs, int rowNum) throws SQLException {
                Class clazz = new Class();
                clazz.setId(rs.getInt("id"));
                clazz.setName(rs.getString("name"));
                clazz.setCreatedTime(rs.getDate("created_time"));
                clazz.setClassDesc(rs.getString("class_desc"));
                clazz.setTeacher1Id(rs.getInt("teacher1_id"));
                clazz.setTeacher2Id(rs.getInt("teacher2_id"));
                clazz.setActivityId(rs.getInt("activity_id"));
                return clazz;
            }
        });
    }

    @Override
    public List<Class> findAllClasses() {
        String sql = "SELECT * FROM t_manager_class";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Class.class));
    }

}
